﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_Flow_AddDocument]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_Flow_AddDocument];
GO
CREATE PROCEDURE [dbo].[sproc_Flow_AddDocument] 
    @DocBuilder nvarchar(300),
    @FlowID int,
    @SQL ntext
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;

DECLARE @Staff_ID int;
DECLARE @Doc_ID int;

SET @Staff_ID =0;

--得到发送者ID
SELECT @Staff_ID = Staff_ID FROM dbo.UDS_Staff WHERE Staff_Name = @DocBuilder;
IF @@rowcount <> 1
BEGIN
    raiserror(N'The specified user "%s" cannot be found.', 16, 1, @DocBuilder);
    return -1;
END;
    
BEGIN TRAN T1;

--填写表单
EXEC(@SQL)
SET @Doc_ID = @@IDENTITY

--填写文档
INSERT INTO dbo.uds_flow_document
    (doc_id,doc_builder_id,doc_added_date,doc_status,flow_id,step_id,isrunning) 
    VALUES(@Doc_ID,@staff_id,getdate(),0,@flowid,1,0);

INSERT INTO dbo.uds_flow_path
    (doc_id,flow_id,step_id,staff_id,order_id)
    SELECT @Doc_id,@flowid,1,@Staff_ID,(SELECT COALESCE(MAX(order_id),0)+1 FROM uds_flow_path WHERE doc_id = @Doc_id) AS order_id;

COMMIT TRAN T1;
RETURN @Doc_ID;

END
GO